﻿using System;
using System.Collections.Generic;
using System.Data;
using CoreLab.Oracle;
using LMD.Lib.Oracle;
using LMD.Lib.DataClass;

namespace Controller
{
    /// <summary>
    /// Summary description for Contact Person Category Controller
    /// </summary>
    public class NPSCPCategoryController : ControllerBase
    {
        public int InsertNPSCPCategoryData(Entities.NPSCPCategory ciCPCategory)
        {
            DBLib dbl = new DBLib();
            List<OracleParameter> lsParams = new List<OracleParameter>();

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "out_new_id";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
            lsParams[lsParams.Count - 1].Direction = ParameterDirection.Output;

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "in_TITLE";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.VarChar;
            lsParams[lsParams.Count - 1].Value = ciCPCategory.sxTitle;

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "in_DETAIL";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.VarChar;
            lsParams[lsParams.Count - 1].Value = ciCPCategory.sxDetail;

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "in_CREATOR";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.VarChar;
            lsParams[lsParams.Count - 1].Value = ICPUser.ID;

            String sxQuery = "PR_I_P_NPS_CP_CATEGORY";

            dbl.ExecuteProcedure(sxQuery, lsParams.ToArray());
            return Convert.ToInt32(lsParams[0].Value);
        }

        public void UpdateNPSCPCategoryData(Entities.NPSCPCategory ciCPCategory)
        {
            DBLib dbl = new DBLib();
            List<OracleParameter> lsParams = new List<OracleParameter>();

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "in_CP_CATEGORY_ID";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
            lsParams[lsParams.Count - 1].Value = ciCPCategory.iID;

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "in_TITLE";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.VarChar;
            lsParams[lsParams.Count - 1].Value = ciCPCategory.sxTitle;

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "in_DETAIL";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.VarChar;
            lsParams[lsParams.Count - 1].Value = ciCPCategory.sxDetail;

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "in_CREATOR";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.VarChar;
            lsParams[lsParams.Count - 1].Value = ICPUser.ID;

            String sxQuery = "PR_U_P_NPS_CP_CATEGORY";

            dbl.ExecuteProcedure(sxQuery, lsParams.ToArray());
        }

        public void DeleteNPSCPCategoryData(Entities.NPSCPCategory ciCPCategory)
        {
            DBLib dbl = new DBLib();
            List<OracleParameter> lsParams = new List<OracleParameter>();

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "in_CP_CATEGORY_ID";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
            lsParams[lsParams.Count - 1].Value = ciCPCategory.iID;

            String sxQuery = "PR_D_P_NPS_CP_CATEGORY";
            dbl.ExecuteProcedure(sxQuery, lsParams.ToArray());
        }

        public List<Entities.NPSCPCategory> NPSCPCategoryList
        {
            get
            {
                DBLib dbl = new DBLib();
                String sxQuery =
                    @"select 
                    P_NPS_CP_CATEGORY_ID,
	                TITLE,
	                DETAIL,
	                CREATE_BY,
	                CREATE_DATE,
	                UPDATE_BY,
	                UPDATE_DATE
                from 
                    P_NPS_CP_CATEGORY
                   WHERE   NVL (VALID_FROM, SYSDATE + 1) < SYSDATE
        AND NVL (VALID_TO, SYSDATE + 1) > SYSDATE
                ";
                DataTable dt = dbl.GetDataTable(sxQuery);

                List<Entities.NPSCPCategory> CPCategoryList = new List<Entities.NPSCPCategory>();
                foreach (DataRow row in dt.Rows)
                {
                    Entities.NPSCPCategory CPCategory = new Entities.NPSCPCategory();
                    CPCategory.iID = Convert.ToInt16(row["P_NPS_CP_CATEGORY_ID"].ToString());
                    CPCategory.sxTitle = row["TITLE"].ToString();
                    CPCategory.sxDetail = row["DETAIL"].ToString();
                    CPCategory.sxCreator = row["CREATE_BY"].ToString();
                    CPCategory.dtmCreated = DateTime.Parse(row["CREATE_DATE"].ToString());
                    CPCategory.sxUpdater = row["UPDATE_BY"].ToString();
                    CPCategory.dtmUpdated = DateTime.Parse(row["UPDATE_DATE"].ToString());
                    CPCategoryList.Add(CPCategory);
                }
                return CPCategoryList;
            }
        }

        public List<Entities.NPSCPCategory> find(string title)
        {
                DBLib dbl = new DBLib();
                String sxQuery =
                    @"select 
                    P_NPS_CP_CATEGORY_ID,
	                TITLE,
	                DETAIL,
	                CREATE_BY,
	                CREATE_DATE,
	                UPDATE_BY,
	                UPDATE_DATE
                from 
                    P_NPS_CP_CATEGORY
                where 
                   NVL (VALID_FROM, SYSDATE + 1) < SYSDATE
        AND NVL (VALID_TO, SYSDATE + 1) > SYSDATE and
                    upper(Title) like upper('%" + title+@"%')
                ";
                DataTable dt = dbl.GetDataTable(sxQuery);

                List<Entities.NPSCPCategory> CPCategoryList = new List<Entities.NPSCPCategory>();
                foreach (DataRow row in dt.Rows)
                {
                    Entities.NPSCPCategory CPCategory = new Entities.NPSCPCategory();
                    CPCategory.iID = Convert.ToInt16(row["P_NPS_CP_CATEGORY_ID"].ToString());
                    CPCategory.sxTitle = row["TITLE"].ToString();
                    CPCategory.sxDetail = row["DETAIL"].ToString();
                    CPCategory.sxCreator = row["CREATE_BY"].ToString();
                    CPCategory.dtmCreated = DateTime.Parse(row["CREATE_DATE"].ToString());
                    CPCategory.sxUpdater = row["UPDATE_BY"].ToString();
                    CPCategory.dtmUpdated = DateTime.Parse(row["UPDATE_DATE"].ToString());
                    CPCategoryList.Add(CPCategory);
                }
                return CPCategoryList;
         
        }

        public Entities.NPSCPCategory getNPSCPCategory(int CPCategoryID)
        {
            DBLib dbl = new DBLib();
            String sxQuery =
                @"select 
                   P_NPS_CP_CATEGORY_ID,
	                TITLE,
	                DETAIL,
	                CREATE_BY,
	                CREATE_DATE,
	                UPDATE_BY,
	                UPDATE_DATE
                from 
                    P_NPS_CP_CATEGORY
                where 
                   NVL (VALID_FROM, SYSDATE + 1) < SYSDATE
        AND NVL (VALID_TO, SYSDATE + 1) > SYSDATE and
                    P_NPS_CP_CATEGORY_ID = :prmID
                ";
            List<OracleParameter> lsParams = new List<OracleParameter>();
            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "prmID";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
            lsParams[lsParams.Count - 1].Value = CPCategoryID;
            DataTable dt = dbl.GetDataTable(sxQuery, lsParams.ToArray());
            Entities.NPSCPCategory CPCategory = new Entities.NPSCPCategory();
            try
            {
                CPCategory.iID = Convert.ToInt16(dt.Rows[0]["P_NPS_CP_CATEGORY_ID"].ToString());
                CPCategory.sxTitle = dt.Rows[0]["TITLE"].ToString();
                CPCategory.sxDetail = dt.Rows[0]["DETAIL"].ToString();
                CPCategory.sxCreator = dt.Rows[0]["CREATE_BY"].ToString();
                CPCategory.dtmCreated = DateTime.Parse(dt.Rows[0]["CREATE_DATE"].ToString());
                CPCategory.sxUpdater = dt.Rows[0]["UPDATE_BY"].ToString();
                CPCategory.dtmUpdated = DateTime.Parse(dt.Rows[0]["UPDATE_DATE"].ToString());

            }
            catch (Exception p) { errLog = p.ToString(); }
            return CPCategory;
        }
    }
}